package com.un.ebs.sale.mapper;

import com.un.ebs.production.domain.ProductionOrderItemExt;
import com.un.ebs.sale.domain.SaleOrder;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.un.ebs.sale.dto.SaleOrderItemDto;
import com.un.ebs.sale.dto.SaleOrderItemPlanDto;
import com.un.ebs.sale.dto.SaleOrderNotInvoiceView;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;

/**
 * <p>
 * Mapper 接口
 * </p>
 *
 * @author liubo
 * @since 2020-05-23
 */
@Component
public interface SaleOrderMapper extends BaseMapper<SaleOrder> {

    @Select("SELECT ISNULL(MAX(RIGHT(code,4)),0) FROM dbo.tb_sale_order WHERE CONVERT(VARCHAR(6),create_time,112)= CONVERT(VARCHAR(6),GETDATE(),112)")
    int maxCode();

    //@Select("SELECT a.sku_id,a.sku_code,a.name_as as sku_name,a.class_id,a.class_name,a.price,a.qty AS contract_qty,ISNULL(b.qty, 0) + ISNULL((SELECT SUM(write_off_qty) FROM dbo.v_contract_item_write_off_ext WHERE formal_contract_code = a.contract_code AND sku_id = a.sku_id ), 0 ) AS sale_order_qty,a.qty - (ISNULL(b.qty, 0) + ISNULL((SELECT SUM(write_off_qty) FROM dbo.v_contract_item_write_off_ext WHERE formal_contract_code = a.contract_code AND sku_id = a.sku_id ),0)) AS qty FROM dbo.v_contract_item_ext a LEFT JOIN dbo.v_sale_order_item_ext b ON a.contract_code = b.contract_code AND a.tenant_id = b.tenant_id AND a.sku_code = b.sku_code WHERE a.qty - (ISNULL(b.qty, 0) + ISNULL((SELECT SUM(write_off_qty) FROM dbo.v_contract_item_write_off_ext WHERE formal_contract_code = a.contract_code AND sku_id = a.sku_id ),0)) > 0 and a.type in (1,2,3,5) and a.is_delete=0 and a.submit_status=1 and a.audit_status=1 AND a.contract_code = #{contractCode} and a.tenant_id=#{tenantId}")
    @Select("SELECT sku_id,sku_code,name_as AS sku_name,class_id,class_name,price,qty-sum_qty AS qty from v_contract_item_ext where contract_code=#{contractCode} and submit_status=1 and audit_status=1 and is_delete=0 and tenant_id=#{tenantId} AND qty-sum_qty>0 order by sku_code")
    List<SaleOrderItemPlanDto> selectContractToOrderPlan(@Param("contractCode") String contractCode, @Param("tenantId") String tenantId);

    @Select("SELECT sku_id, price, qty-sum_qty as qty from v_contract_item_ext where contract_id=#{contractId} and is_delete=0 AND qty-sum_qty>0 order by sku_code")
    List<SaleOrderItemDto> queryNotRunContractItem(@Param("contractId") String contractId);

    @Select("select top 1 * from v_production_order_item_ext where sku_id = #{skuId} and sale_order_id=#{saleOrderId} and submit_status=1")
    ProductionOrderItemExt queryProductionItemBySkuId(@Param("skuId") String skuId,@Param("saleOrderId") String saleOrderId);

    @Select("select isnull(sum(qty),0) from v_production_order_item_ext where sku_id = #{skuId} and sale_order_id=#{saleOrderId} and submit_status=1")
    int queryProductionItemQtyBySkuId(@Param("skuId") String skuId,@Param("saleOrderId") String saleOrderId);

    @Select("select order_id,code as order_code,a.customer_ent_id as ent_id,a.customer_ent_name as ent_name,a.contract_code,a.sku_id,a.sku_code,a.name_as as sku_name,sum(a.qty) as qty,sum(a.qty*a.price) as money,a.tenant_id from v_sale_order_item_sum_ext as a  where a.submit_status=1 and a.unit_type=2 and a.tenant_id=#{tenantId} and a.order_id not in (select a.sale_order_id from tb_invoice_note_by_sale a,tb_sale_order b,tb_invoice_note c where a.note_id=c.id and a.sale_order_id=b.id and b.submit_status=1 and c.is_delete=0) group by order_id,code,customer_ent_id,customer_ent_name,contract_code,tenant_id,a.sku_id,a.sku_code,a.name_as")
    List<SaleOrderNotInvoiceView> queryNotInvoiceByService(@Param("tenantId") String tenantId);

    @Select("SELECT isnull(SUM(write_off_qty),0) FROM v_contract_item_write_off_ext WHERE formal_contract_code=#{contractCode} and sku_id=#{skuId}")
    int queryWriteOffQty(@Param("contractCode") String contractCode,@Param("skuId") String skuId);
}
